SQL¶
Day 1¶
Day 2¶
Day 3¶
Day 4¶
Day 5¶
SQLBOLT¶
SQL Lesson 2: Queries with constraints (Pt. 1)¶
SQL Lesson 3: Queries with constraints (Pt. 2)¶
Find All Toy Story Movies
Find all movies not directed by John Lasseter
Find all WALL-* movies
_ (underscore) is Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) col_name LIKE "AN_"
(matches "AND", but not "AN")
SQL Lesson 4: Filtering and sorting Query results¶
08-11-23 Instructor-led REVIEW of SQL¶
Subquery¶
Use of Having ?¶
The WHERE clause is used to filter rows before they are grouped and aggregated, while the HAVING clause is used to filter the results of aggregate functions applied to grouped data.
The HAVING clause is used to filter the results of aggregate functions applied to grouped data. It operates on groups of rows created using the GROUP BY clause. It's used to filter the grouped data based on aggregate results like sum, count, average, etc.
HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions
highest payment¶
Game show
- The Entity Relationship Diagram (ERD) are output from the following database design phase:
A. Logical Database Design - where you do your data modeling and mapping. talk about relationships between the data and general schema for the database
conceptual phase - first phase. higher ups take over and conceptualize.
physical database design - last phase. phyically build it out.
- First quiz of Database
Basic building block: ..... restrictions placed on the data
A. Constraint
- ___ describes an association among entities
B. Relationship
- ____ represent the data that we are collecting about the entities that we are storing about
c. entity?
ex. columns are attributes and entities are values within the columns
- an attribute is a charactaritstic of an entity
an ___ is a business object that represents a group or category of data.
C. entity
- the object we collect data about is an entity. the data we collect is an
C. Where
A. IFNULL
A. Having (used to filter groups that specify conditions)
WE CANNOT USE THE OPERATOR '=' FOR NULL
NO TWO NULLS ARE EQUAL.
YOU MUST USE THE BUILT-IN FUNCTIONS IS NULL OR IF NULL. CANNOT USE EQUAL SIGN
THEREFORE THE OUTPUT OF THIS IS D. NO VALUES (THIS CODE IS FULLY FUNCTIONAL, SO NO SYNTAX ERROR)
THERE WILL NEVER BE A SHIPMENT DATE THAT = NULL, SO IT WILL ALWAYS RETURN NO VALUE.
C. ON Clause
b) constraint
DDL data definintion language is the SQL subsystem that does constraints
B) filters rows that are not included in a specified set of values
B) defines a unique identifier for each row in the table
best practice charactaristics of a primary key = unique, not null, best if auto increments, best if its numeric,
C. Aggregate (aggregate functions perform calculations based on the GROUP BY clause to aggregte things like SUM(), AVG())
Join
A) Distinct
D) Ensure the integrity of database operations as a single, automic unit.
B) UPDATE (you update records)
Alter (to alter the schema)
B) ADD
INSERT INTO is an alternate clause to add whole new rows while specifying values
In summary, INSERT INTO is used to add new rows with data to an existing table, while ADD is used within an ALTER TABLE statement to add new columns to an existing table's structure. They serve different purposes in manipulating the database schema and data.
ON KBA
KNOW WHAT A CROSS JOIN IS FOR KBA
AND KNOW WHAT CARTESIAN IS FOR KBA